
--sp_UpdateUser.sql
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_UpdateUser]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sp_UpdateUser]
GO
CREATE PROC sp_UpdateUser
	@id						int,
	@email					varchar(200),
	@address				varchar(200),
	@firstname				nvarchar(50),
	@lastname				nvarchar(100),
	@phone					varchar(20),
	@birthday				varchar(20),
	@accountType			nvarchar(200)

AS
BEGIN
	DECLARE @accType int
	SELECT ID=@accType FROM AccoutType WHERE [NAME] = @accountType

	Update Account
		SET AccountType = @accType WHERE ID = @id

	UPDATE AccountInformation
		SET
			FirstName = @firstname,
			LastName = @lastname,
			Email = @email,
			Address = @address,
			Phone = @phone,
			Birthday = @birthday
		WHERE
			AccountID = @id
END

GO

--sp_UpdateProduct.sql
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_UpdateProduct]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sp_UpdateProduct]
GO
CREATE PROC sp_UpdateProduct
	@id			int,
	@cateID		int,
	@proName	nvarchar(100),
	@image		varchar(200),
	@des		nvarchar(2000),
	@price		float
AS
BEGIN
	UPDATE Product
		SET
			CategoryID = @cateID,
			ProductName = @proName, 
			Image = @image, 
			Description = @des, 
			Price = @price
		WHERE
			ID = @id
END
GO

--sp_GetUserByID.sql
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_GetUserByID]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sp_GetUserByID]
GO
CREATE PROC sp_GetUserByID
	@id	int
AS
BEGIN
	SELECT A.ID, A.Username, A.Password, A.Salt, B.Name As AccountType,
		 C.FirstName, C.LastName, C.Email, C.Address, C.Phone,
		 cast(datepart(mm,C.Birthday) as varchar) As BMonth,
		 cast(datepart(dd,C.Birthday) as varchar) As BDay,
		 cast(datepart(yyyy,C.Birthday) as varchar) As BYear
		FROM Account As A INNER JOIN AccountType As B ON A.AccountType = B.ID
			INNER JOIN AccountInformation As C On A.ID = C.AccountID
			WHERE A.ID = @id
END
GO

--sp_UpdateCategories.sql
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_UpdateCategories]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sp_UpdateCategories]
GO
CREATE PROC sp_UpdateCategories
	@cateName		nvarchar(200),
	@cateDes		nvarchar(2000),
	@image			varchar(200)
AS
BEGIN
	UPDATE categories
		SET
			CategoryName = @cateName,
			Description = @cateDes,
			[Image] = @image
END
GO

--sp_GetUserByRole.sql
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_GetUserByRole]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sp_GetUserByRole]
GO
CREATE PROC sp_GetUserByRole
	@role	int
AS
BEGIN
	SELECT A.ID, A.Username, A.Password, A.Salt, B.Name As AccountType,
		 C.FirstName, C.LastName, C.Email, C.Address, C.Phone,
		 cast(datepart(mm,C.Birthday) as varchar) As BMonth,
		 cast(datepart(dd,C.Birthday) as varchar) As BDay,
		 cast(datepart(yyyy,C.Birthday) as varchar) As BYear
		FROM Account As A INNER JOIN AccountType As B ON A.AccountType = B.ID
			INNER JOIN AccountInformation As C On A.ID = C.AccountID
			WHERE A.AccountType = @role
END
GO

--sp_InsertFactory.sql
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_InsertFactory]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sp_InsertFactory]
GO
CREATE PROC sp_InsertFactory
	@facName	nvarchar(200),
	@phone		varchar(20),
	@facDes		nvarchar(2000)
AS
BEGIN
	INSERT INTO Factory
	(
		FactoryName, Phone, Description, Status
	)
	VALUES
	(
		@facName, @phone, @facDes, 1
	)
END
GO

--sp_GetAllFactory.sql
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_GetAllFactory]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sp_GetAllFactory]
GO
CREATE PROC sp_GetAllFactory
AS
BEGIN
	SELECT ID, FactoryName, Phone, Description, Status FROM Factory
END
GO

--sp_GetAllProductByProID.sql
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_GetAllProductByProID]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sp_GetAllProductByProID]
GO
CREATE PROC sp_GetAllProductByProID
	@proID	int
AS
BEGIN
	SELECT A.ID, B.CategoryName, A.ProductName, A.Image, A.Description, A.Price 
		FROM Product As A INNER JOIN Categories As B ON A.CategoryID = B.ID
		WHERE A.ID = @proID
END
GO

--sp_InsertUser.sql
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_InsertUser]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sp_InsertUser]
GO
CREATE PROC sp_InsertUser
	@Username				nvarchar(40),
	@Password				nvarchar(40),
	@Email					varchar(200),
	@Address				varchar(200),
	@Firstname				nvarchar(50),
	@Lastname				nvarchar(100),
	@Phone					varchar(20),
	@Birthday				varchar(20)

AS
BEGIN
	INSERT INTO Account(
			Username,
			Password,
			AccountType
		)
		VALUES(
			@Username,
			@Password,
			1
		)

	DECLARE @AccountID int
	SELECT @AccountID = ID FROM Account WHERE Username = @Username

	INSERT INTO AccountInformation(
			AccountID,
			FirstName,
			LastName,
			Email,
			Address,
			Phone,
			Birthday
		)
		VALUES(
			@AccountID,
			@Firstname,
			@Lastname,
			@Email,
			@Address,
			@Phone,
			@Birthday
		)	
END

GO

--sp_InsertProduct.sql
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_InsertProduct]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sp_InsertProduct]
GO
CREATE PROC sp_InsertProduct
	@cateID		int,
	@proName	nvarchar(100),
	@image		varchar(200),
	@des		nvarchar(2000),
	@price		float
AS
BEGIN
	INSERT INTO Product
	(
		CategoryID, ProductName, Image, Description, Price
	)
	VALUES
	(
		@cateID, @proName, @image, @des, @price
	)
END
GO

--sp_FindAllUser.sql
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_FindAllUser]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sp_FindAllUser]
GO
CREATE PROC sp_FindAllUser
AS
BEGIN
	SELECT A.ID, A.Username, A.Password, A.Salt, B.Name,
		C.FirstName, C.LastName, C.Email, C.Address, C.Phone,
		cast(datepart(mm,C.Birthday) as varchar) As BMonth,
		cast(datepart(dd,C.Birthday) as varchar) As BDay,
		cast(datepart(yyyy,C.Birthday) as varchar) As BYear
		FROM Account As A
			INNER JOIN AccountType As B ON A.AccountType = B.ID
			INNER JOIN AccountInformation As C ON A.ID = C.AccountID
END

GO

--sp_GetAllProduct.sql
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_GetAllProduct]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sp_GetAllProduct]
GO
CREATE PROC sp_GetAllProduct
AS
BEGIN
	SELECT A.ID, B.CategoryName, A.ProductName, A.Image, A.Description, A.Price 
		FROM Product As A INNER JOIN Categories As B ON A.CategoryID = B.ID
END
GO

--sp_UpdateFactory.sql
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_UpdateFactory]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sp_UpdateFactory]
GO
CREATE PROC sp_UpdateFactory
	@id			int,
	@facName	nvarchar(200),
	@phone		varchar(20),
	@facDes		nvarchar(2000),
	@status		int
AS
BEGIN
	UPDATE Factory
		SET
			FactoryName = @facName, Phone = @phone, Description = @facDes, Status = @status
		WHERE ID = @id
END
GO

--sp_GetInsertCategories.sql
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_GetInsertCategories]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sp_GetInsertCategories]
GO
CREATE PROC sp_GetInsertCategories
	@cateName		nvarchar(200),
	@cateDes		nvarchar(2000),
	@image			varchar(200)
AS
BEGIN
	INSERT INTO categories (
		CategoryName,
		Description,
		[Image]
	)
		VALUES(
		@cateName,
		@cateDes,
		@image
	)
END
GO

--sp_FindUserByUsername.sql
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_FindUserByUsername]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sp_FindUserByUsername]
GO
CREATE PROC sp_FindUserByUsername
	@Username				nvarchar(40)
AS
BEGIN
	SELECT Username FROM Account WHERE Username = @Username
END

GO

--sp_GetAllCategories.sql
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_GetAllCategories]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sp_GetAllCategories]
GO
CREATE PROC sp_GetAllCategories
AS
BEGIN
	SELECT ID, CategoryName As CateName, Description As CateDes, [Image] from Categories
END
GO

--sp_GetMenuByRole.sql
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_GetMenuByRole]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sp_GetMenuByRole]
GO
CREATE PROC sp_GetMenuByRole
	@role  varchar(30)
AS
BEGIN
	SELECT A.ID, A.Role, B.MenuName, B.URL
		FROM UserMenu As A INNER JOIN Menu As B ON A.MenuID = B.ID
		WHERE A.Role = @role
END
GO

--sp_GetUserByUsername.sql
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_GetUserByUsername]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sp_GetUserByUsername]
GO
CREATE PROC sp_GetUserByUsername
	@username varchar(20)
AS
BEGIN
	SELECT A.ID, A.Username, A.Password, A.Salt, B.Name As AccountType,
		 C.FirstName, C.LastName, C.Email, C.Address, C.Phone,
		 cast(datepart(mm,C.Birthday) as varchar) As BMonth,
		 cast(datepart(dd,C.Birthday) as varchar) As BDay,
		 cast(datepart(yyyy,C.Birthday) as varchar) As BYear
		FROM Account As A INNER JOIN AccountType As B ON A.AccountType = B.ID
			INNER JOIN AccountInformation As C On A.ID = C.AccountID
			WHERE A.Username = @username
END
GO

--sp_GetAllProductByCateID.sql
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_GetAllProductByCateID]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[sp_GetAllProductByCateID]
GO
CREATE PROC sp_GetAllProductByCateID
	@cateID	int
AS
BEGIN
	SELECT A.ID, B.CategoryName, A.ProductName, A.Image, A.Description, A.Price 
		FROM Product As A INNER JOIN Categories As B ON A.CategoryID = B.ID
		WHERE A.CategoryID = @cateID
END
GO
